package com.zzmcc.jms.controller;

import com.jfinal.aop.Before;
import com.jfinal.ext.interceptor.excel.ExcelUploadInterceptor;
import com.jfinal.ext.kit.PoiExt;

import com.jfinal.ext.render.excel.JxlsRender;
import com.jfinal.kit.PathKit;
import com.jfinal.log.Logger;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.upload.UploadFile;
import com.zzmcc.base.BaseController;
import com.zzmcc.common.AuthorizeInterceptor;
import com.zzmcc.common.DefaultExcelUploadInterceptor;
import com.zzmcc.jms.model.Demand;
import com.zzmcc.jms.model.Project;

import util.*;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

import static com.zzmcc.jms.common.ModelConfigWms.TABLE_Demand;

/***
 * 需求管理
 */
public class DemandController extends BaseController {
    private static final Logger log =  Logger.getLogger(DemandController.class);
 	public void index() {

        StringBuffer whee = new StringBuffer();
        List<Object> param = new ArrayList<Object>();
        String name = getPara("demand.name");
		String cusName = getPara("demand.cusName");
		String memo = getPara("demand.memo");
		String demandMemo = getPara("demand.demandMemo");
		String code = getPara("demand.code");
		String isValid = getPara("demand.isValid");
		String isCrm = getPara("demand.isCrm");
		String crmid = getPara("demand.crmid");


		String demandOwner = getPara("demand.demandOwner");
        String beginDate = getPara("beginDate");
		String endDate = getPara("endDate");
		String homeCounty = getPara("demand.homeCounty");

        if(ChristStringUtil.isNotEmpty(name)){
            whee.append(" and name like ?");
            param.add("%" + name + "%");
        }

		if(ChristStringUtil.isNotEmpty(crmid)){
			whee.append(" and crmid like ?");
			param.add("%" + crmid + "%");
		}


		if(ChristStringUtil.isNotEmpty(cusName)){
			whee.append(" and cusName like ?");
			param.add("%" + cusName + "%");
		}

		if(ChristStringUtil.isNotEmpty(demandMemo)){
			whee.append(" and demandMemo like ?");
			param.add("%" + demandMemo + "%");
		}

		if(ChristStringUtil.isNotEmpty(memo)){
			whee.append(" and memo like ?");
			param.add("%" + memo + "%");
		}

		if(ChristStringUtil.isNotEmpty(code)){
			whee.append(" and code like ?");
			param.add("%" + code + "%");
		}
		if(ChristStringUtil.isNotEmpty(demandOwner)){
			whee.append(" and demandOwner = ?");
			param.add(demandOwner);
		}

		if(ChristStringUtil.isNotEmpty(homeCounty)){
			whee.append(" and homeCounty = ?");
			param.add(homeCounty);
		}
		if(ChristStringUtil.isNotEmpty(isValid)){
			whee.append(" and isValid = ?");
			param.add(isValid);
		}


		if(ChristStringUtil.isNotEmpty(isCrm)){
			whee.append(" and COALESCE(isCrm,'否') = ?");
			param.add(isCrm);
		}


		if(ChristStringUtil.isNotEmpty(beginDate)){
			whee.append(" and TO_DAYS(createDate) >= TO_DAYS(?)");
			param.add( beginDate );
		}
		if(ChristStringUtil.isNotEmpty(endDate)){
			whee.append(" and TO_DAYS(createDate) <= TO_DAYS(?)");
			param.add(endDate);
		}
		Page<Demand> pager = Demand.dao.paginate(
				getParaToInt("pageNum", 1),
				getParaToInt("numPerPage", 20),
                "select * ",
                " from " + TABLE_Demand +" where 1=1 "+ whee.toString()+"order by modifyDate desc",
                param.toArray());

		setAttr("pager",pager);
		setAttr("name",name);
		setAttr("cusName",cusName);
		setAttr("memo",memo);
		setAttr("demandMemo",demandMemo);
		setAttr("code",code);
		setAttr("demandOwner",demandOwner);
		setAttr("isValid",isValid);
		setAttr("isCrm",isCrm);
		setAttr("crmid",crmid);


		setAttr("beginDate",beginDate);
		setAttr("endDate",endDate);
		setAttr("homeCounty",homeCounty);
		render("../demand_list.html");

	}

	@Before(AuthorizeInterceptor.class)
	public void add() {
		render("../demand_input.html");
	}

	public void save() {
		Demand m = getModel(Demand.class, "demand");
		ModelUtils.fillForSave(m);
		if(StringUtil.isEmpty(m.getStr("did"))){
			ModelUtils.fillUUID(m, "did");
		}
		if(StringUtil.isNotEmpty(m.getStr("crmid"))){
			m.set("isCrm","是");
		}
		m.save();
		renderDWZSuccessJson("添加成功！");
	}

	@Before(AuthorizeInterceptor.class)
	public void edit() {
		Demand demand = Demand.dao.findById(getPara("id"));
		setAttr("demand", demand);
		render("../demand_input.html");
	}

	public void update() {
		Demand m = getModel(Demand.class, "demand");
		if(StringUtil.isEmpty(m.getStr("did"))){
			ModelUtils.fillUUID(m,"did");
		}
		if(StringUtil.isNotEmpty( m.getStr("crmid"))){
			m.set("isCrm","是");
		}
		ModelUtils.fillForUpdate(m);
		m.update();
		renderDWZSuccessJson("修改成功！");
	}

	

	public void view() {
		setAttr("project",Project.dao.findById(getPara("id")));
		render("../demand_detail.html");
	}

	@Before(AuthorizeInterceptor.class)
	public void imp() {
		render("../demand_input_imp.html");
	}

	public void impData() {
		String path = new SimpleDateFormat("yyyy/MM/dd").format(new Date());
		UploadFile file = getFile();
		String fileName = null;
		if (file !=null) {
			File source = file.getFile();
			fileName = file.getFileName();
			try {

				FileInputStream fis = new FileInputStream(source);
				File targetDir = new File(PathKit.getWebRootPath()  + "/u/" + path);
				if (!targetDir.exists()) {
					targetDir.mkdirs();
				}
				File target = new File(targetDir, fileName);
				if (!target.exists()) {
					target.createNewFile();
				}
				FileOutputStream fos = new FileOutputStream(target);
				byte[] bts = new byte[300];
				while (fis.read(bts, 0, 300) != -1) {
					fos.write(bts, 0, 300);
				}
				fos.close();
				fis.close();
				source.delete();

				String templateFileName = PathKit.getWebRootPath()  + "/u/" + path +"/" +fileName;
				String sql = "insert into jms_demand (did,cusName,city,homeCounty,demandMemo,name,scene," +
						"submitDate,longitude,latitude,addr," +
						"radius,space,sysetemCnt,systemBand," +
						"aerialHeight,aerialCnt,isRRu,memo) " +
						"values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
				Map<String, Object> map = PoiExt.ReadExcel(templateFileName,"铁塔类需求收集表", sql,5,1,19);

				renderDWZSuccessJson("总记录数:"+map.get("count")+"；成功导入:"+map.get("success")+"；失败数"+map.get("lost"));

			} catch (FileNotFoundException e) {
				renderDWZErrorJson("上传出现错误，请稍后再上传");
			}
			catch (Exception e){
				renderDWZErrorJson("导入失败，请重新上传"+e.getMessage());
			}
		}

	}

	public void export() {

		//导出条件
		StringBuffer whee = new StringBuffer();
		List<Object> param = new ArrayList<Object>();

		String cusName = getPara("demand.cusName");
		if(ChristStringUtil.isNotEmpty(cusName)){
			whee.append(" and cusName like ?");
			param.add("%" + cusName + "%");
		}

		String sql = "select *  from " + TABLE_Demand +" where 1=1 "+ whee.toString()+"order by modifyDate desc";
		List<Record> demandDetail = Db.find(sql, param.toArray());

		Map<String, Object> beans = new HashMap<String, Object>();
		beans.put("demandDetail", demandDetail);

		String templateFileName = PathKit.getRootClassPath() + "/tpl_exp_demand.xls";
		String filename = "demand_list.xls";
		render(JxlsRender.me(templateFileName).filename(filename).beans(beans));

	}

	public void transConfirm() {
		//需求确认
		String[] ids = getParaValues("ids");
		for(String id:ids) {
			Demand m = Demand.dao.findById(id);
			String isValid = m.getStr("isValid");
			if ( isValid.equals("否")){
				m.set("isValid", "是");
				ModelUtils.fillUUID(m,"did");
			}
			ModelUtils.fillForUpdate(m);
			m.update();
		}
		renderDWZSuccessJson("需求批量确认成功!");

	}


	@Before(AuthorizeInterceptor.class)
	public void delete() {


		String[] ids = getParaValues("ids");
		for(String id:ids){
			Demand.dao.deleteById(id);
		}
		renderDWZSuccessJson("删除成功!");

	}

	public void down(){

		String templateFileName = PathKit.getRootClassPath() + "/tpl_imp_demand.xls";

		renderFile(new File(templateFileName));
	}

	/**
	 * 产品价格测算
	 */
	public void imp2() {
		render("../demand_input_imp2.html");
	}

	public void down2(){

		String templateFileName = PathKit.getRootClassPath() + "/tpl_imp_demand2.xls";

		renderFile(new File(templateFileName));
	}

	public void impData2() {
		String path = new SimpleDateFormat("yyyy/MM/dd").format(new Date());
		UploadFile file = getFile();
		String fileName = null;
		if (file !=null) {
			File source = file.getFile();
			fileName = file.getFileName();
			try {
				FileInputStream fis = new FileInputStream(source);
				File targetDir = new File(PathKit.getWebRootPath()  + "/u/" + path);
				if (!targetDir.exists()) {
					targetDir.mkdirs();
				}
				File target = new File(targetDir, fileName);
				if (!target.exists()) {
					target.createNewFile();
				}
				FileOutputStream fos = new FileOutputStream(target);
				byte[] bts = new byte[300];
				while (fis.read(bts, 0, 300) != -1) {
					fos.write(bts, 0, 300);
				}
				fos.close();
				fis.close();
				source.delete();
				//1.清空临时表
				Db.update("delete a from imp_demand_fee a");
				String templateFileName = PathKit.getWebRootPath()  + "/u/" + path +"/" +fileName;
				//2.导入数据
				String sql = "insert into imp_demand_fee (cusName,homeCounty,name,scene,addr," +
						"productType,systemCnt,aerialHeight,productConfig,buildType) " +
						"values(?,?,?,?,?,?,?,?,?,?)";
				Map<String, Object> map = PoiExt.ReadExcel(templateFileName, "铁塔类需求收集表", sql, 5, 2, 10);
				renderDWZSuccessJson("总记录数:"+map.get("count")+"；成功导入:"+map.get("success")+"；失败数"+map.get("lost"));

				//3.导出数据
				sql ="\n" +
						"select a.id,a.cusName,a.homeCounty,a.`name`,a.scene,a.addr,a.productType,a.systemCnt,a.aerialHeight,a.productConfig,a.buildType,\n" +
						"round(((c.`铁塔`*(case when a.`productType` in('普通地面塔','景观塔') then d.adjustValue else 1 end)/10+c.`机房`/(CASE when a.`productConfig`='自建机房' then 20 else 6 end)+c.`配套`/6)*1.02+0.3770)*1.15,4) newBase,\n" +
						"round(((c.`铁塔`/10+c.`机房`/(CASE when c.`机房类型`='自建机房' then 20 else 6 end)+c.`配套`/6)*0.73*1.02+0.3770)*1.15,4) oldBase ,b.`包干价`/10000 siteFee,\n" +
						"c.`铁塔` towerCost,c.`机房` roomCost,c.`配套` auxCost\n" +
						"from (\n" +
						"SELECT a.id,a.cusName,a.homeCounty,a.`name`,a.addr,a.buildType,\n" +
						"\t\t case\n" +
						"\t\t\t\twhen a.`productType` LIKE '%普通地面塔%' then '普通地面塔'\n" +
						"\t\t\t\twhen a.`productType` LIKE '%景观塔%' then '景观塔'\n" +
						"\t\t\t\twhen a.`productType` LIKE '%简易塔%' then '简易塔'\n" +
						"\t\t\t\twhen a.`productType` LIKE '%普通楼面塔%' then '普通楼面塔'\n" +
						"\t\t\t\twhen a.`productType` LIKE '%楼面抱杆%' then '楼面抱杆'  \n" +
						"\t\t\t\telse '楼面抱杆' end 'productType',\n" +
						"\t\ta.systemCnt,\n" +
						"\t\t\tcase\n" +
						"\t\t\t\twhen a.`productConfig` LIKE '%自建%' then '自建机房'\n" +
						"\t\t\t\twhen a.`productConfig` LIKE '%租赁%' then '租赁机房'\n" +
						"\t\t\t\twhen a.`productConfig` LIKE '%一体化柜%' then '一体化柜'\n" +
						"\t\t\t\twhen a.`productConfig` LIKE '%RRU拉远%' then 'RRU拉远'\n" +
						"\t\t\t\twhen a.`productConfig` LIKE '%无机房%' then '无机房及配套'  \n" +
						"\t\t\t\telse '一体化柜 'end 'productConfig',\n" +
						"\t\t\tCASE WHEN a.`productType` like '%普通地面塔%' THEN\n" +
						"\t\t\t\t\t\t\tcase when a.`aerialHeight`='30≤H<35' then '30≤H<35'\n" +
						"\t\t\t\t\t\t\t\t\t when a.`aerialHeight`='35≤H<40' then '35≤H<40'\n" +
						"\t\t\t\t\t\t\t\t\t when a.`aerialHeight`='40≤H<45' then '40≤H<45'\n" +
						"\t\t\t\t\t\t\t\t\t when a.`aerialHeight`='45≤H<50' then '45≤H<50'\n" +
						"\t\t\t\t\t\t\telse 'H<30' end\n" +
						"\n" +
						"\t\t\t\t\t\tWHEN a.`productType` like '%景观塔%' THEN\n" +
						"\t\t\t\t\t\t\tcase when a.`aerialHeight` ='20≤H<25' then '20≤H<25'\n" +
						"\t\t\t\t\t\t\t\t\t when a.`aerialHeight` ='25≤H<30' then '25≤H<30'\n" +
						"\t\t\t\t\t\t\t\t\t when a.`aerialHeight` ='30≤H<35' then '30≤H<35'\n" +
						"\t\t\t\t\t\t\t\t\t when a.`aerialHeight` ='35≤H≤40' then '35≤H≤40'\n" +
						"\t\t\t\t\t\t\telse 'H<20' end\n" +
						"\t\t\tELSE '-'\n" +
						"\t\t\tEND  'aerialHeight',\n" +
						"case when a.`addr` like '%芗城%' or a.`addr` like '%龙文%' then '市区' \n" +
						"\twhen \n" +
						"\t a.`addr` LIKE '%石码%' or \n" +
						"\t a.`addr` LIKE '%莆美%' or \n" +
						"\t a.`addr` LIKE '%绥安%' or \n" +
						"\t a.`addr` LIKE '%小溪%' or \n" +
						"\t a.`addr` LIKE '%武安%' or \n" +
						"\t a.`addr` LIKE '%山城%' or \n" +
						"\t a.`addr` LIKE '%南诏%' or \n" +
						"\t a.`addr` LIKE '%西埔%' OR  \n" +
						"\t a.`addr` LIKE '%丰山%' or\n" +
						"\t a.`addr` LIKE '%角美%' or\n" +
						"\t a.`addr` LIKE '%龙池%'  then '城镇'\n" +
						"\telse  '农村' end 'scene',\n" +
						"case when a.`productConfig` LIKE '%机房%' then '有机房' else '无机房 'end '机房类型',\n" +
						"case when a.`productConfig` LIKE '%一次性%' then '一次性' else '周期性 'end '包干性质'\n" +
						"from  imp_demand_fee a ) a\n" +
						"LEFT JOIN rent.sys_bg b\n" +
						"on a.`机房类型`=b.`机房类型`\n" +
						"AND a.`scene`=b.`场景`\n" +
						"and a.`包干性质` = b.`包干性质`\n" +
						"LEFT JOIN rent.sys_base c\n" +
						"on a.`productType`=c.`产品类型`\n" +
						"and a.`productConfig`=c.`机房类型`\n" +
						"and a.`aerialHeight`=c.`挂高`\n" +
						"LEFT JOIN rent.sys_area_wind d\n" +
						"on INSTR(a.homeCounty,d.`homeCounty`)>0";
				List<Record> fee = Db.find(sql);
				Map<String, Object> beans = new HashMap<String, Object>();
				beans.put("fee", fee);
				templateFileName = PathKit.getRootClassPath() + "/tpl_exp_demand_fee.xls";
				String filename = "demand_fee.xls";
				render(JxlsRender.me(templateFileName).filename(filename).beans(beans));

			} catch (FileNotFoundException e) {
				renderDWZErrorJson("上传出现错误，请稍后再上传");
			}catch (Exception e){
				renderDWZErrorJson("导入失败，请重新上传"+e.getMessage());
			}
		}

	}


}
